﻿using Groep2TINGHService.Get_Set_Classes;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Groep2TINGHService.DB_Classes
{
    public class CompanyDB
    {
        //Geeft een lijst terug met alle Companies
        public static List<Company> GetAllCompanies()
        {
            List<Company> lijst = new List<Company>();
            SqlConnection connection = new SqlConnection(ConnectionDB.GetConnection());
            SqlCommand selectcommand = new SqlCommand("select * from Company", connection);
            try
            {
                Company temp;
                connection.Open();
                SqlDataReader reader = selectcommand.ExecuteReader();
                int Id = reader.GetOrdinal("Id");
                int Name = reader.GetOrdinal("Name");
                int Street = reader.GetOrdinal("Street");
                int ZipCode = reader.GetOrdinal("ZipCode");
                int City = reader.GetOrdinal("City");
                int Country = reader.GetOrdinal("Country");
                int Email = reader.GetOrdinal("Email");
                int Phone = reader.GetOrdinal("Phone");
                int Employees = reader.GetOrdinal("Employees");

                while (reader.Read())
                {
                    temp = new Company();
                    temp.Id = reader.GetGuid(Id);
                    temp.Name = reader.GetString(Name);
                    temp.Street = reader.GetString(Street);
                    temp.ZipCode = reader.GetInt32(ZipCode);
                    temp.City = reader.GetString(City);
                    temp.Country = reader.GetString(Country);
                    temp.Email = reader.GetString(Email);
                    temp.Phone = reader.GetString(Phone);
                    temp.Employees = reader.GetInt32(Employees);
                    lijst.Add(temp);

                }
                reader.Close();
                return lijst;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        //Voegt een nieuwe Company toe
        public static bool InsertCompany(Company company)
        {
            SqlConnection connection = null;
            string insert = "insert into Company(Name,Street,Zipcode,City,Country,Email,Phone,Employees)" +
                            "Values(@Name,@Street,@Zipcode,@City,@Country,@Email,@Phone,@Employees);";
            SqlCommand insertCommand = new SqlCommand(insert, connection);
            insertCommand.Parameters.AddWithValue("@Name", company.Name);
            insertCommand.Parameters.AddWithValue("@Street", company.Street);
            insertCommand.Parameters.AddWithValue("@Zipcode", company.ZipCode);
            insertCommand.Parameters.AddWithValue("@City", company.City);
            insertCommand.Parameters.AddWithValue("@Country", company.Country);
            insertCommand.Parameters.AddWithValue("@Email", company.Email);
            insertCommand.Parameters.AddWithValue("@Phone", company.Phone);
            insertCommand.Parameters.AddWithValue("@Employees", company.Employees);
           
            try
            {
                using (connection = new SqlConnection(ConnectionDB.GetConnection()))
                {
                    connection.Open();
                    int count = insertCommand.ExecuteNonQuery();
                    if (count > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

        }

        //Verwijdert een Company via Id
        public static bool DeleteCompany(string name)
        {
            SqlConnection connection = new SqlConnection(ConnectionDB.GetConnection());
            string delete = "DELETE FROM Company WHERE Name = @name";
            SqlCommand deleteCommand = new SqlCommand(delete, connection);
            deleteCommand.Parameters.AddWithValue("@name", name);
            try
            {
                connection.Open();
                int count = deleteCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        //Wijzigen van een Company
        public static bool ModifyCompany(Company company,string name)
        {
            SqlConnection connection = new SqlConnection(ConnectionDB.GetConnection());
            string modify = "UPDATE Company "+ 
                            "SET Street=@Street, Zipcode=@Zipcode, City=@City, Country=@Country, Email=@Email, Phone=@Phone, Employees=@Employees "+ 
                            "WHERE Name = @Name";
            SqlCommand modifyCommand = new SqlCommand(modify, connection);
            modifyCommand.Parameters.AddWithValue("@Name", name);
            modifyCommand.Parameters.AddWithValue("@Street", company.Street);
            modifyCommand.Parameters.AddWithValue("@Zipcode", company.ZipCode);
            modifyCommand.Parameters.AddWithValue("@City", company.City);
            modifyCommand.Parameters.AddWithValue("@Country", company.Country);
            modifyCommand.Parameters.AddWithValue("@Email", company.Email);
            modifyCommand.Parameters.AddWithValue("@Phone", company.Phone);
            modifyCommand.Parameters.AddWithValue("@Employees", company.Employees);


            try
            {
                connection.Open();
                int count = modifyCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

    }
}